Course: MySQL Training
Topic: Implementing Joins, Subqueries, Views, and Stored Procedures
Course Link: MySQL Training Course
Demo Link: Register for a Free Course Demo
MySQL provides several powerful features for retrieving, organizing, processing, and reusing data. Joins, subqueries, views, and stored procedures are important SQL concepts used in database-driven applications such as e-commerce platforms, banking systems, student management systems, inventory applications, and customer relationship management systems.
Joins combine related records from multiple tables. Subqueries allow one SQL query to use the result of another query. Views provide reusable virtual tables based on SQL statements. Stored procedures allow developers to save and execute a group of SQL statements on the database server.
These features help developers write organized SQL, reduce repetitive query logic, simplify reporting, and maintain consistency in database operations.
Consider an e-commerce database containing customers, products, orders, and order_items tables. A developer may need to display customer names with their orders, find products priced above average, create a reusable sales report, or execute several order-processing statements through a single stored procedure.
Joins, subqueries, views, and stored procedures provide different ways to solve these requirements.
After completing these notes, learners will be able to:
Before implementing these concepts, learners should understand basic SQL statements and relational database design.
The examples use syntax supported by modern MySQL 8.x releases. Some features and constraint behaviors can vary between MySQL versions.
We will create a small database containing customers, orders, and products. These tables will be used throughout the notes to demonstrate joins, subqueries, views, and stored procedures.
CREATE DATABASE sql_advanced_demo;
USE sql_advanced_demo;The CREATE DATABASE statement creates a database, while USE selects it for subsequent operations.
CREATE TABLE customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(150) NOT NULL UNIQUE,
city VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock_quantity INT NOT NULL DEFAULT 0
);CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
status VARCHAR(30) NOT NULL DEFAULT 'Pending',
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
);CREATE TABLE order_items (
order_item_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
CONSTRAINT fk_items_order
FOREIGN KEY (order_id)
REFERENCES orders(order_id),
CONSTRAINT fk_items_product
FOREIGN KEY (product_id)
REFERENCES products(product_id)
);INSERT INTO customers
(first_name, last_name, email, city)
VALUES
('Rahul', 'Sharma', '[email protected]', 'Mumbai'),
('Priya', 'Patel', '[email protected]', 'Pune'),
('Amit', 'Verma', '[email protected]', 'Mumbai'),
('Neha', 'Singh', '[email protected]', 'Delhi'),
('Karan', 'Mehta', '[email protected]', 'Nashik');INSERT INTO products
(product_name, price, stock_quantity)
VALUES
('Laptop', 55000.00, 10),
('Smartphone', 25000.00, 20),
('Headphones', 1500.00, 50),
('Keyboard', 2500.00, 30),
('Mouse', 800.00, 40);INSERT INTO orders
(customer_id, order_date, status)
VALUES
(1, '2026-01-10', 'Delivered'),
(2, '2026-01-12', 'Processing'),
(1, '2026-02-05', 'Delivered'),
(3, '2026-02-15', 'Pending');INSERT INTO order_items
(order_id, product_id, quantity, unit_price)
VALUES
(1, 1, 1, 55000.00),
(1, 3, 2, 1500.00),
(2, 2, 1, 25000.00),
(2, 5, 2, 800.00),
(3, 4, 1, 2500.00),
(4, 3, 3, 1500.00);These sample inserts assume the newly created tables are empty and that generated identifiers begin at 1. If you already have data, retrieve the actual IDs before inserting related records.
A JOIN combines records from two or more tables using a relationship or matching condition. Joins are especially useful when information is divided across related tables.
For example, the customers table stores customer names, while the orders table stores customer identifiers. A join can combine these tables to display each order with the customer's name.
INNER JOIN returns only records that satisfy the join condition in both tables. Records without a match are excluded from the result.
SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;SELECT
c.customer_id,
CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
o.order_id,
o.order_date,
o.status
FROM customers AS c
INNER JOIN orders AS o
ON c.customer_id = o.customer_id
ORDER BY o.order_date;Explanation:
customers AS c gives the customers table the alias c.orders AS o gives the orders table the alias o.Customers who have not placed orders do not appear in this INNER JOIN result.
SELECT
o.order_id,
p.product_name,
oi.quantity,
oi.unit_price,
oi.quantity * oi.unit_price AS line_total
FROM orders AS o
INNER JOIN order_items AS oi
ON o.order_id = oi.order_id
INNER JOIN products AS p
ON oi.product_id = p.product_id
ORDER BY o.order_id;This query joins three tables to display the products included in each order and calculate each order line's total value.
LEFT JOIN returns all records from the left table and matching records from the right table. When there is no match, columns from the right table contain NULL.
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;SELECT
c.customer_id,
CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
o.order_id,
o.order_date
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
ORDER BY c.customer_id;This query displays every customer, including customers who have never placed an order.
SELECT
c.customer_id,
c.first_name,
c.last_name,
c.email
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;The WHERE condition selects customers for whom no matching order exists.
Conditions placed in the WHERE clause can change the results of an outer join. For example, filtering the right-side table for a particular status in WHERE may remove customers who have no matching order.
SELECT c.customer_id, c.first_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
AND o.status = 'Delivered';In this example, every customer is retained, but only delivered orders are matched.
RIGHT JOIN returns every record from the right table and matching records from the left table. When no matching left-side record exists, the left-side columns contain NULL.
SELECT
c.customer_id,
c.first_name,
o.order_id,
o.status
FROM customers AS c
RIGHT JOIN orders AS o
ON c.customer_id = o.customer_id;This query returns all orders and their matching customer records. Because orders.customer_id is a foreign key in our schema, each order should have a valid customer.
RIGHT JOIN is often rewritten as LEFT JOIN by reversing the table order. Many developers prefer LEFT JOIN because it can be easier to read consistently.
CROSS JOIN produces a Cartesian product: each row from the first table is combined with every row from the second table.
SELECT
c.first_name,
p.product_name
FROM customers AS c
CROSS JOIN products AS p;If the customers table contains five records and the products table contains five records, the query produces 25 combinations.
CROSS JOIN can be useful for generating combinations, such as customer-product test cases. However, it can produce a very large result if the tables contain many rows.
A self join joins a table to itself. It is useful when records in the same table have relationships with one another.
Consider an employee table where each employee may report to another employee. The manager_id column references the employee_id column in the same table.
CREATE TABLE employees (
employee_id INT AUTO_INCREMENT PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
manager_id INT NULL,
CONSTRAINT fk_employee_manager
FOREIGN KEY (manager_id)
REFERENCES employees(employee_id)
);INSERT INTO employees (employee_name, manager_id)
VALUES
('Asha', NULL),
('Vikram', 1),
('Meera', 1),
('Rohan', 2);SELECT
e.employee_name AS employee,
m.employee_name AS manager
FROM employees AS e
LEFT JOIN employees AS m
ON e.manager_id = m.employee_id;The aliases e and m allow the same table to be referenced in two roles. LEFT JOIN keeps employees who do not have a manager.
Complex business reports often require data from several tables. Each additional join connects another related entity.
SELECT
o.order_id,
o.order_date,
CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
p.product_name,
oi.quantity,
oi.unit_price,
oi.quantity * oi.unit_price AS line_total,
o.status
FROM orders AS o
INNER JOIN customers AS c
ON o.customer_id = c.customer_id
INNER JOIN order_items AS oi
ON o.order_id = oi.order_id
INNER JOIN products AS p
ON oi.product_id = p.product_id
ORDER BY o.order_date DESC, o.order_id;This query combines information from customers, orders, order_items, and products. It is useful for order details pages and sales reports.
SELECT
o.order_id,
CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders AS o
INNER JOIN customers AS c
ON o.customer_id = c.customer_id
INNER JOIN order_items AS oi
ON o.order_id = oi.order_id
GROUP BY
o.order_id,
c.customer_id,
c.first_name,
c.last_name
ORDER BY order_total DESC;SUM calculates the total of all order-item line values for each order. This example excludes taxes, shipping charges, discounts, and refunds.
A subquery is a SELECT statement placed inside another SQL statement. It is also known as an inner query or nested query. The statement containing the subquery is called the outer query.
Subqueries can be used in SELECT, FROM, WHERE, and HAVING clauses, depending on the query structure.
SELECT columns
FROM table_name
WHERE column_name operator (
SELECT column_name
FROM another_table
WHERE condition
);A single-row subquery returns one row, often containing one value. It can be used with comparison operators such as =, greater than, less than, and not equal to.
SELECT product_id, product_name, price
FROM products
WHERE price > (
SELECT AVG(price)
FROM products
);The inner query calculates the average price of all products. The outer query returns products whose prices are greater than that average.
SELECT product_name, price
FROM products
WHERE price = (
SELECT MAX(price)
FROM products
);This query returns every product whose price equals the maximum price, including ties.
A multiple-row subquery returns more than one row. Operators such as IN, ANY, ALL, and EXISTS can be used with appropriate subqueries.
SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
);The inner query returns customer identifiers from the orders table. IN selects customers whose identifiers appear in that result.
SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id NOT IN (
SELECT customer_id
FROM orders
WHERE customer_id IS NOT NULL
);The IS NOT NULL condition avoids a common issue with NOT IN. If a NOT IN subquery returns NULL, comparisons can evaluate to UNKNOWN and unexpectedly exclude rows.
For finding customers without orders, NOT EXISTS is often a clear and robust alternative.
EXISTS checks whether a subquery returns at least one row. NOT EXISTS checks whether it returns no rows.
SELECT
c.customer_id,
c.first_name,
c.last_name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
);The subquery is correlated because it refers to the current row from the outer customers query.
SELECT
c.customer_id,
c.first_name,
c.last_name
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
);NOT EXISTS returns customers for whom no matching order is found.
A correlated subquery refers to one or more columns from the outer query. Conceptually, its condition depends on the current outer row.
For this example, assume the products table has a category_id column. Add it if you are using the earlier sample schema.
ALTER TABLE products
ADD COLUMN category_id INT NULL;The following query compares each product with the average price of products in the same category.
SELECT
p.product_id,
p.product_name,
p.category_id,
p.price
FROM products AS p
WHERE p.category_id IS NOT NULL
AND p.price > (
SELECT AVG(p2.price)
FROM products AS p2
WHERE p2.category_id = p.category_id
);The inner query uses p.category_id from the outer query. Products without a category are excluded because the comparison is intended to be within a defined category.
| Feature | Non-Correlated Subquery | Correlated Subquery |
|---|---|---|
| Dependency | Independent of the outer query. | References the outer query. |
| Example | Price above the overall average. | Price above the average for the current category. |
| Evaluation | Can often be evaluated independently. | Depends on the outer row, though the optimizer may transform it. |
| Performance | Often straightforward to optimize. | Depends on indexes, data size, and optimizer transformations. |
ANY and ALL compare a value with the values returned by a subquery.
The condition is true if the comparison is true for at least one value returned by the subquery.
SELECT product_name, price
FROM products
WHERE price > ANY (
SELECT price
FROM products
WHERE price < 3000
);This query returns products priced higher than at least one product priced below ₹3,000. The exact result depends on the products in the database.
The condition is true if the comparison is true for every value returned by the subquery.
SELECT product_name, price
FROM products
WHERE price > ALL (
SELECT price
FROM products
WHERE price < 3000
);This query returns products priced higher than every value returned by the subquery. Remember that empty result sets and NULL values can affect logical outcomes.
A subquery in the FROM clause creates a derived table. MySQL requires a derived table to have an alias.
SELECT
customer_totals.customer_id,
customer_totals.total_spending
FROM (
SELECT
o.customer_id,
SUM(oi.quantity * oi.unit_price) AS total_spending
FROM orders AS o
INNER JOIN order_items AS oi
ON o.order_id = oi.order_id
GROUP BY o.customer_id
) AS customer_totals
WHERE customer_totals.total_spending > 10000;The inner query calculates total order-item values per customer. The outer query filters customers whose calculated totals exceed ₹10,000.
A scalar subquery in the SELECT list can return one value for each row processed by the outer query.
SELECT
c.customer_id,
CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
(
SELECT COUNT(*)
FROM orders AS o
WHERE o.customer_id = c.customer_id
) AS total_orders
FROM customers AS c
ORDER BY total_orders DESC;The subquery calculates the number of orders belonging to each customer.
For large datasets, compare this approach with a JOIN and GROUP BY query using EXPLAIN. A join-based aggregation may be more suitable depending on the query plan and workload.
A Common Table Expression (CTE) is a named result set defined using WITH. It can make complex SQL easier to read and maintain.
WITH customer_spending AS (
SELECT
o.customer_id,
SUM(oi.quantity * oi.unit_price) AS total_spending
FROM orders AS o
INNER JOIN order_items AS oi
ON o.order_id = oi.order_id
GROUP BY o.customer_id
)
SELECT
c.customer_id,
c.first_name,
c.last_name,
cs.total_spending
FROM customer_spending AS cs
INNER JOIN customers AS c
ON cs.customer_id = c.customer_id
WHERE cs.total_spending > 10000
ORDER BY cs.total_spending DESC;CTEs are useful for separating calculation steps from the final query. MySQL supports CTEs in modern releases, including MySQL 8.0 and later.
A view is a named SQL query that can be accessed like a virtual table. A standard view generally stores the query definition rather than a separate copy of the query results.
Views are useful for simplifying complex queries, standardizing reporting logic, and limiting access to selected columns or rows.
The CREATE VIEW statement defines a view based on a SELECT query.
CREATE VIEW view_name AS
SELECT columns
FROM table_name
WHERE condition;CREATE VIEW customer_orders_view AS
SELECT
c.customer_id,
CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
c.email,
o.order_id,
o.order_date,
o.status
FROM customers AS c
INNER JOIN orders AS o
ON c.customer_id = o.customer_id;The view combines customer information with order details.
SELECT *
FROM customer_orders_view
ORDER BY order_date DESC;Applications can query this view without repeating the full join statement each time.
Sales reporting frequently requires joining orders with order_items and calculating totals.
CREATE VIEW order_sales_view AS
SELECT
o.order_id,
o.customer_id,
o.order_date,
o.status,
SUM(oi.quantity * oi.unit_price) AS item_total
FROM orders AS o
INNER JOIN order_items AS oi
ON o.order_id = oi.order_id
GROUP BY
o.order_id,
o.customer_id,
o.order_date,
o.status;SELECT *
FROM order_sales_view
WHERE item_total > 10000
ORDER BY item_total DESC;This view represents recorded order-item totals. It does not calculate taxes, shipping, discounts, refunds, or payment settlement.
Views can be inspected, replaced, and dropped using standard MySQL statements.
SHOW FULL TABLES
WHERE Table_type = 'VIEW';SHOW CREATE VIEW customer_orders_view;CREATE OR REPLACE VIEW customer_orders_view AS
SELECT
c.customer_id,
c.first_name,
c.last_name,
o.order_id,
o.order_date,
o.status
FROM customers AS c
INNER JOIN orders AS o
ON c.customer_id = o.customer_id;DROP VIEW IF EXISTS customer_orders_view;Dropping a view removes its definition, not the underlying customer or order records.
Some views support INSERT, UPDATE, and DELETE operations, but not every view is updatable. Simple views based on a single table may be updatable if they meet MySQL's requirements.
Views containing aggregate functions, GROUP BY, DISTINCT, UNION, or certain joins and expressions may not support direct updates, or may have restrictions.
CREATE VIEW mumbai_customers_view AS
SELECT
customer_id,
first_name,
last_name,
email,
city
FROM customers
WHERE city = 'Mumbai';Depending on the view definition and its check options, updates made through this simple view may be allowed.
CREATE OR REPLACE VIEW mumbai_customers_view AS
SELECT
customer_id,
first_name,
last_name,
email,
city
FROM customers
WHERE city = 'Mumbai'
WITH CASCADED CHECK OPTION;WITH CHECK OPTION prevents updates or inserts through an updatable view when the resulting row would no longer satisfy the view's WHERE condition. This helps keep changes consistent with the view's filtering rule.
Views can help limit exposure of sensitive data. For example, an application may need customer names and cities but should not need every customer field.
CREATE VIEW customer_public_view AS
SELECT
customer_id,
first_name,
last_name,
city
FROM customers;Database administrators can grant selected users permission to query this view rather than granting access to the full customers table.
Views are not a replacement for a complete security design. Permissions on the view and underlying tables, the view's security context, and the application's access model must be configured carefully.
A stored procedure is a named collection of SQL statements stored in the database. It can be called whenever its logic is needed.
Stored procedures can accept input parameters, return values through output parameters, use local variables, execute conditional logic, perform loops, and group multiple database operations.
| Feature | View | Stored Procedure |
|---|---|---|
| Purpose | Represents a reusable query. | Executes a sequence of database statements. |
| Parameters | Does not accept ordinary procedure-style parameters. | Supports IN, OUT, and INOUT parameters. |
| Execution | Queried using SELECT or other supported statements. | Called using CALL. |
| Control flow | Does not contain procedural loops and conditionals. | Can contain IF, CASE, loops, and handlers. |
| Typical use | Reusable reporting and data access. | Multi-step operations and reusable database workflows. |
Stored procedures are created using CREATE PROCEDURE. When using MySQL Workbench or the command-line client, the delimiter is often changed temporarily so semicolons inside the procedure body are not interpreted as the end of the entire CREATE statement.
DELIMITER //
CREATE PROCEDURE procedure_name()
BEGIN
SQL_statement_1;
SQL_statement_2;
END //
DELIMITER ;DELIMITER //
CREATE PROCEDURE GetAllCustomers()
BEGIN
SELECT
customer_id,
first_name,
last_name,
email,
city
FROM customers
ORDER BY customer_id;
END //
DELIMITER ;DELIMITER // changes the client-side statement delimiter. It allows the procedure body to contain semicolons. The delimiter is restored after the procedure is created.
CALL GetAllCustomers();CALL executes the stored procedure and returns its result set.
An IN parameter supplies a value to a stored procedure. It is suitable for filtering records or performing calculations based on a user-provided value.
DELIMITER //
CREATE PROCEDURE GetCustomerById(IN p_customer_id INT)
BEGIN
SELECT
customer_id,
first_name,
last_name,
email,
city
FROM customers
WHERE customer_id = p_customer_id;
END //
DELIMITER ;CALL GetCustomerById(1);The procedure returns the customer whose identifier matches the supplied value. If no customer exists with that identifier, the query returns no rows.
DELIMITER //
CREATE PROCEDURE GetProductsBelowPrice(IN p_max_price DECIMAL(10,2))
BEGIN
SELECT
product_id,
product_name,
price,
stock_quantity
FROM products
WHERE price < p_max_price
ORDER BY price;
END //
DELIMITER ;Execute it with:
CALL GetProductsBelowPrice(5000.00);This returns products priced below the supplied maximum value.
An OUT parameter returns a value to the caller. It is useful when a procedure needs to return a count, total, or other calculated result separately from a result set.
DELIMITER //
CREATE PROCEDURE GetCustomerCount(OUT p_total_customers INT)
BEGIN
SELECT COUNT(*)
INTO p_total_customers
FROM customers;
END //
DELIMITER ;CALL GetCustomerCount(@customer_total);
SELECT @customer_total AS total_customers;The procedure stores the count in the OUT parameter, and the caller reads it through a session variable.
DELIMITER //
CREATE PROCEDURE GetOrderTotal(
IN p_order_id INT,
OUT p_order_total DECIMAL(12,2)
)
BEGIN
SELECT COALESCE(SUM(quantity * unit_price), 0)
INTO p_order_total
FROM order_items
WHERE order_id = p_order_id;
END //
DELIMITER ;Call it using:
CALL GetOrderTotal(1, @order_total);
SELECT @order_total AS order_total;If the order has no order-item records, the result is zero. This calculation represents the sum of order-item values only.
An INOUT parameter accepts an initial value and can return a modified value to the caller.
DELIMITER //
CREATE PROCEDURE IncreaseAmountByPercent(
INOUT p_amount DECIMAL(12,2),
IN p_percentage DECIMAL(5,2)
)
BEGIN
SET p_amount = p_amount + (p_amount * p_percentage / 100);
END //
DELIMITER ;Call the procedure using a session variable:
SET @amount = 1000.00;
CALL IncreaseAmountByPercent(@amount, 10.00);
SELECT @amount AS updated_amount;The initial amount of 1000.00 becomes 1100.00 after a 10 percent increase.
Local variables store temporary values during procedure execution. They are declared with DECLARE near the beginning of a BEGIN...END block.
DELIMITER //
CREATE PROCEDURE GetProductStatistics()
BEGIN
DECLARE v_product_count INT DEFAULT 0;
DECLARE v_average_price DECIMAL(10,2) DEFAULT 0;
SELECT COUNT(*), COALESCE(AVG(price), 0)
INTO v_product_count, v_average_price
FROM products;
SELECT
v_product_count AS total_products,
v_average_price AS average_price;
END //
DELIMITER ;Execute the procedure:
CALL GetProductStatistics();Local variables exist within their declared scope. They are different from session variables such as @amount, which are referenced using an @ prefix.
Conditional statements allow a procedure to execute different SQL statements depending on a condition.
DELIMITER //
CREATE PROCEDURE CheckProductStock(IN p_product_id INT)
BEGIN
DECLARE v_stock INT DEFAULT NULL;
SELECT stock_quantity
INTO v_stock
FROM products
WHERE product_id = p_product_id;
IF v_stock IS NULL THEN
SELECT 'Product not found' AS message;
ELSEIF v_stock = 0 THEN
SELECT 'Out of stock' AS message;
ELSE
SELECT 'Product available' AS message;
END IF;
END //
DELIMITER ;This procedure checks whether a product exists and whether its stock is zero or greater than zero. It assumes product_id is a primary key, so the SELECT INTO query can return at most one row.
CALL CheckProductStock(1);Replace 1 with the identifier of a product you want to check.
CASE provides another way to select behavior based on a value or condition.
DELIMITER //
CREATE PROCEDURE GetOrderStatusMessage(IN p_order_id INT)
BEGIN
DECLARE v_status VARCHAR(30) DEFAULT NULL;
SELECT status
INTO v_status
FROM orders
WHERE order_id = p_order_id;
CASE
WHEN v_status IS NULL THEN
SELECT 'Order not found' AS message;
WHEN v_status = 'Pending' THEN
SELECT 'Order is waiting for confirmation' AS message;
WHEN v_status = 'Processing' THEN
SELECT 'Order is being prepared' AS message;
WHEN v_status = 'Delivered' THEN
SELECT 'Order has been delivered' AS message;
ELSE
SELECT 'Order has another status' AS message;
END CASE;
END //
DELIMITER ;This example uses a searched CASE statement to return a message based on the order status.
MySQL stored procedures support iterative constructs such as WHILE, REPEAT, and LOOP. Loops are useful for repeated procedural operations, although set-based SQL is generally preferable when the same task can be completed with a single query.
DELIMITER //
CREATE PROCEDURE DisplayNumbers(IN p_limit INT)
BEGIN
DECLARE v_counter INT DEFAULT 1;
CREATE TEMPORARY TABLE IF NOT EXISTS number_results (
number_value INT
);
TRUNCATE TABLE number_results;
WHILE v_counter <= p_limit DO
INSERT INTO number_results(number_value)
VALUES (v_counter);
SET v_counter = v_counter + 1;
END WHILE;
SELECT number_value
FROM number_results
ORDER BY number_value;
DROP TEMPORARY TABLE number_results;
END //
DELIMITER ;Call the procedure using:
CALL DisplayNumbers(5);The procedure creates a temporary table, inserts numbers from 1 through the supplied limit, displays them, and removes the temporary table. This example is for learning control flow rather than a typical production reporting pattern.
For a negative or zero limit, the loop executes zero times and returns an empty result set. Validate parameters when a particular range is required.
Stored procedures can use handlers to respond to SQL conditions. Error handling is important when a procedure performs multiple database operations.
DELIMITER //
CREATE PROCEDURE ExampleTransaction()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;
START TRANSACTION;
UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE product_id = 1
AND stock_quantity > 0;
IF ROW_COUNT() <> 1 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Product unavailable or out of stock';
END IF;
COMMIT;
END //
DELIMITER ;The EXIT handler catches SQL exceptions, rolls back the transaction, and rethrows the error using RESIGNAL so the caller can detect failure.
This example demonstrates transaction and error-handling syntax. A real order-placement procedure must also create the order and its items, validate all requested quantities, and handle payment integration through a carefully designed application workflow.
A stored procedure may include START TRANSACTION, COMMIT, and ROLLBACK when the procedure owns the transaction boundary. This helps group related data changes into one unit.
DELIMITER //
CREATE PROCEDURE UpdateProductPrice(
IN p_product_id INT,
IN p_new_price DECIMAL(10,2)
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;
IF p_new_price < 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Price cannot be negative';
END IF;
START TRANSACTION;
UPDATE products
SET price = p_new_price
WHERE product_id = p_product_id;
IF ROW_COUNT() = 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Product not found or price unchanged';
END IF;
COMMIT;
END //
DELIMITER ;This example illustrates a validation and transaction pattern. Note that ROW_COUNT() can be zero when an UPDATE makes no actual change, even if the product exists. If unchanged prices should be accepted, check for product existence separately and adjust the logic accordingly.
Applications should agree on who owns the transaction. Procedures that start and commit transactions may not be suitable for callers that need to group several procedure calls into one larger transaction.
A stored procedure can contain joins to provide reusable reports.
DELIMITER //
CREATE PROCEDURE GetCustomerOrderHistory(
IN p_customer_id INT
)
BEGIN
SELECT
o.order_id,
o.order_date,
o.status,
COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS order_total
FROM orders AS o
LEFT JOIN order_items AS oi
ON o.order_id = oi.order_id
WHERE o.customer_id = p_customer_id
GROUP BY
o.order_id,
o.order_date,
o.status
ORDER BY o.order_date DESC;
END //
DELIMITER ;Call the procedure:
CALL GetCustomerOrderHistory(1);This procedure returns order history for a selected customer. The LEFT JOIN ensures that an order without order-item rows can still appear with a zero total.
Views and stored procedures can be used together. A view can provide a reusable reporting dataset, while a stored procedure can apply parameters and execute additional business logic.
CREATE OR REPLACE VIEW order_detail_report AS
SELECT
o.order_id,
o.customer_id,
o.order_date,
o.status,
p.product_id,
p.product_name,
oi.quantity,
oi.unit_price,
oi.quantity * oi.unit_price AS line_total
FROM orders AS o
INNER JOIN order_items AS oi
ON o.order_id = oi.order_id
INNER JOIN products AS p
ON oi.product_id = p.product_id;DELIMITER //
CREATE PROCEDURE GetOrderDetailReport(
IN p_order_id INT
)
BEGIN
SELECT *
FROM order_detail_report
WHERE order_id = p_order_id
ORDER BY product_name;
END //
DELIMITER ;Execute the procedure:
CALL GetOrderDetailReport(1);The view defines the common order-detail query, while the procedure filters it using the supplied order identifier.
SHOW PROCEDURE STATUS
WHERE Db = 'sql_advanced_demo';SHOW CREATE PROCEDURE GetAllCustomers;DROP PROCEDURE IF EXISTS GetAllCustomers;Dropping a stored procedure removes the procedure definition. It does not delete the customer records or the tables used by the procedure.
MySQL does not provide a direct CREATE OR REPLACE PROCEDURE statement equivalent to CREATE OR REPLACE VIEW. To change a procedure, use ALTER PROCEDURE for supported characteristics or drop and recreate the procedure definition as appropriate. Keep the procedure source under version control.
| Feature | Joins | Subqueries |
|---|---|---|
| Primary purpose | Combine related records. | Use the result of one query in another. |
| Typical use | Display orders with customer names. | Find products above average price. |
| Readability | Often clear for multi-table relationships. | Often clear for nested conditions. |
| Performance | Depends on indexes and the query plan. | Depends on query structure and optimizer transformations. |
| Best practice | Use explicit join conditions. | Choose IN, EXISTS, or scalar subqueries according to the requirement. |
Neither joins nor subqueries are universally faster. MySQL may transform a subquery into a join or use another execution strategy. Compare actual execution plans and performance for important queries.
Performance depends on the query, the amount of data, indexes, server configuration, and the optimizer's execution plan. Joins, subqueries, views, and procedures should all be reviewed as part of the application's overall database design.
EXPLAIN
SELECT
c.customer_id,
c.first_name,
o.order_id
FROM customers AS c
INNER JOIN orders AS o
ON c.customer_id = o.customer_id
WHERE c.customer_id = 1;EXPLAIN shows how MySQL plans to execute the query. Examine access types, possible and selected keys, estimated rows, and other available plan details.
CREATE INDEX idx_orders_customer_id
ON orders(customer_id);The orders.customer_id column is frequently used in joins and filters. However, inspect existing indexes first, since a foreign key may already have a suitable index.
SELECT order_id, order_date, status
FROM orders
WHERE customer_id = 1;Selecting only the required columns reduces unnecessary data transfer and makes query intent clearer.
Joining multiple one-to-many relationships can multiply rows. Before calculating totals, confirm that each row represents the intended business unit and that the joins do not duplicate amounts.
| Problem | Possible Cause | Solution |
|---|---|---|
| Unknown column | A column name is misspelled or does not exist. | Use DESCRIBE table_name to inspect the schema. |
| Ambiguous column | Multiple tables contain the same column name. | Qualify the column using its table alias. |
| Duplicate rows in a join | One-to-many relationships produce multiple matches. | Review join conditions and aggregation requirements. |
| Unexpected NULL values | A LEFT or RIGHT JOIN has no matching record. | Check join conditions and use COALESCE only when appropriate. |
| Subquery returns more than one row | A multi-row result is used with a single-value comparison. | Use IN, EXISTS, or an appropriate aggregate. |
| NOT IN returns unexpected results | The subquery may contain NULL. | Filter NULL values or use NOT EXISTS. |
| View cannot be updated | The view contains aggregation or other non-updatable constructs. | Update the base table through a controlled statement or redesign the view. |
| Procedure syntax error | Incorrect delimiters or BEGIN/END structure. | Review the procedure body and delimiter handling. |
| Procedure not found | Wrong database, name, or procedure was not created. | Check the selected schema and SHOW PROCEDURE STATUS. |
| Access denied for procedure | The account lacks required privileges. | Review EXECUTE and relevant object permissions. |
| Transaction does not roll back expected changes | Nontransactional tables or implicit commits may be involved. | Use transactional storage engines such as InnoDB and review transaction boundaries. |
Complete these exercises to reinforce your understanding of advanced MySQL features.
Project Objective: Build a reusable reporting system that uses joins, subqueries, views, and stored procedures to analyze customer orders.
CREATE OR REPLACE VIEW customer_spending_view AS
SELECT
c.customer_id,
c.first_name,
c.last_name,
COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS total_spending
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
LEFT JOIN order_items AS oi
ON o.order_id = oi.order_id
GROUP BY
c.customer_id,
c.first_name,
c.last_name;SELECT *
FROM customer_spending_view
ORDER BY total_spending DESC;A JOIN combines related records from two or more tables based on a join condition.
INNER JOIN returns matching records from both tables. LEFT JOIN returns all rows from the left table and matching rows from the right table, with NULL values for unmatched right-side columns.
A self join joins a table to itself, often to represent hierarchical relationships such as employees and managers.
A subquery is a query nested inside another SQL statement. It can provide a value or result set for the outer query.
IN checks whether a value belongs to a returned set. EXISTS checks whether the subquery returns at least one row. Their performance depends on the query structure and optimizer.
A correlated subquery refers to a column from the outer query, so its logic depends on the current outer row.
A view is a named SQL query that can be queried like a virtual table. A standard view usually stores its definition rather than a separate result set.
Some views are updatable, particularly simple views. Views with aggregates, GROUP BY, DISTINCT, UNION, or other complex constructs have restrictions.
It prevents inserts or updates through an updatable view when the resulting row does not satisfy the view's filtering condition.
A stored procedure is a named collection of SQL statements stored in the database and executed using CALL.
A stored procedure is invoked with CALL and can return result sets and OUT parameters. A stored function returns a value and can be used in expressions where permitted.
IN supplies input to a procedure, OUT returns a value, and INOUT accepts an initial value and returns a modified value.
It changes the client-side statement terminator so semicolons inside the procedure body do not prematurely end the CREATE PROCEDURE statement.
A handler specifies how a procedure responds to a SQL condition, such as an exception or a NOT FOUND condition.
RESIGNAL passes a handled condition or error back to the caller, optionally with modified condition information.
Yes. A procedure can use transaction statements such as START TRANSACTION, COMMIT, and ROLLBACK when appropriate. The transaction ownership and caller expectations must be designed carefully.
When one row matches multiple rows in another table, the join returns multiple combinations. This is expected behavior for one-to-many relationships and must be considered during aggregation.
Inspect the execution plan using EXPLAIN, review indexes and join conditions, select only required columns, and test with representative data.
Joins, subqueries, views, and stored procedures are essential MySQL concepts for building maintainable and efficient database applications. Joins connect related records across tables, subqueries provide intermediate results for filtering and calculations, views simplify reusable queries, and stored procedures centralize multi-step database logic.
Understanding how these features work together enables developers to create customer reports, order summaries, product analyses, and reusable database workflows. Correct use of constraints, transactions, indexes, permissions, and error handling makes these solutions more reliable and secure.
Practice the examples using the sample database, modify the queries, and test different scenarios to build confidence with advanced SQL.
Learn More: Explore the MySQL Training Course to develop your SQL and database management skills.
Course Demo: Visit Register for a Free Course Demo to learn more about the training program.
